import requests
import mysql.connector
from mysql.connector import Error
from datetime import datetime, date
from typing import List, Dict, Any, Set, Optional
from utils.logger import sync_logger
from config.gitlab_config import GITLAB_CONFIG
from config.db_config import DB_CONFIG, GITLAB_DB_CONFIG
from services.db_service import DBService

def init_tables():
    """自动建表，确保相关表存在"""
    try:
        conn = mysql.connector.connect(**GITLAB_DB_CONFIG)
        cursor = conn.cursor()
        # users表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INT PRIMARY KEY,
                username VARCHAR(255),
                name VARCHAR(255),
                email VARCHAR(255),
                state VARCHAR(50),
                created_at DATETIME,
                last_activity_at DATETIME,
                avatar_url VARCHAR(255),
                web_url VARCHAR(255),
                is_admin BOOLEAN,
                external BOOLEAN,
                organization VARCHAR(255)
            )
        ''')
        # project_members表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS project_members (
                id INT AUTO_INCREMENT PRIMARY KEY,
                project_id INT,
                user_id INT,
                access_level INT,
                created_at DATETIME,
                expires_at DATETIME,
                UNIQUE KEY unique_project_user (project_id, user_id)
            )
        ''')
        # developer_statistics表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS developer_statistics (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                username VARCHAR(255),
                project_id INT NOT NULL,
                commit_count INT DEFAULT 0,
                mr_created_count INT DEFAULT 0,
                mr_merged_count INT DEFAULT 0,
                mr_reviewed_count INT DEFAULT 0,
                issue_closed_count INT DEFAULT 0,
                review_pass_rate FLOAT DEFAULT 0.0,
                coverage FLOAT DEFAULT 0.0,
                stats_date DATE NOT NULL,
                UNIQUE KEY unique_user_project_date (user_id, project_id, stats_date)
            )
        ''')
        conn.commit()
        cursor.close()
        conn.close()
        sync_logger.log_info("自动建表完成")
    except Exception as e:
        sync_logger.log_error(f"自动建表失败: {e}")

def _parse_datetime(date_str: Optional[str]) -> Optional[datetime]:
    """安全地解析日期时间字符串"""
    if not date_str:
        return None
    try:
        return datetime.fromisoformat(date_str.replace('Z', '+00:00'))
    except (ValueError, AttributeError):
        return None

def get_connection():
    """获取数据库连接"""
    return mysql.connector.connect(**GITLAB_DB_CONFIG)

def get_existing_members() -> Set[int]:
    """获取已存在的成员ID集合"""
    connection = None
    cursor = None
    try:
        connection = get_connection()
        if connection.is_connected():
            cursor = connection.cursor()
            cursor.execute("SELECT id FROM users")
            return {row[0] for row in cursor.fetchall()}
    except Error as e:
        sync_logger.log_error(f"获取已存在成员时发生错误: {e}")
    finally:
        if cursor:
            cursor.close()
        if connection and connection.is_connected():
            connection.close()
    return set()

def get_existing_project_members() -> Set[tuple]:
    """获取已存在的项目成员关系集合"""
    connection = None
    cursor = None
    try:
        connection = get_connection()
        if connection.is_connected():
            cursor = connection.cursor()
            cursor.execute("SELECT project_id, user_id FROM project_members")
            return {(row[0], row[1]) for row in cursor.fetchall()}
    except Error as e:
        sync_logger.log_error(f"获取已存在项目成员关系时发生错误: {e}")
    finally:
        if cursor:
            cursor.close()
        if connection and connection.is_connected():
            connection.close()
    return set()

def get_all_members() -> List[Dict[str, Any]]:
    """获取所有成员信息"""
    with sync_logger.start_timer("获取成员列表"):
        headers = {'PRIVATE-TOKEN': GITLAB_CONFIG['token']}
        members = []
        page = 1
        
        while True:
            url = f"{GITLAB_CONFIG['url']}/api/{GITLAB_CONFIG['api_version']}/users"
            params = {'page': page, 'per_page': GITLAB_CONFIG['per_page']}
            
            try:
                response = requests.get(url, headers=headers, params=params, timeout=GITLAB_CONFIG['timeout'])
                if response.status_code == 200:
                    page_members = response.json()
                    if not page_members:
                        break
                    members.extend(page_members)
                    page += 1
                else:
                    sync_logger.log_error(f"获取成员列表失败: {response.status_code}")
                    break
            except Exception as e:
                sync_logger.log_error(f"获取成员列表时发生错误: {e}")
                break
        
        sync_logger.log_info(f"共获取到 {len(members)} 个成员")
        return members

def get_project_members(project_id: int) -> List[Dict[str, Any]]:
    """获取指定项目的成员信息"""
    with sync_logger.start_timer(f"获取项目 {project_id} 的成员列表"):
        headers = {'PRIVATE-TOKEN': GITLAB_CONFIG['token']}
        members = []
        page = 1
        
        while True:
            url = f"{GITLAB_CONFIG['url']}/api/{GITLAB_CONFIG['api_version']}/projects/{project_id}/members"
            params = {'page': page, 'per_page': GITLAB_CONFIG['per_page']}
            
            try:
                response = requests.get(url, headers=headers, params=params, timeout=GITLAB_CONFIG['timeout'])
                if response.status_code == 200:
                    page_members = response.json()
                    if not page_members:
                        break
                    members.extend(page_members)
                    page += 1
                else:
                    sync_logger.log_error(f"获取项目 {project_id} 的成员列表失败: {response.status_code}")
                    break
            except Exception as e:
                sync_logger.log_error(f"获取项目 {project_id} 的成员列表时发生错误: {e}")
                break
        
        sync_logger.log_info(f"项目 {project_id} 共获取到 {len(members)} 个成员")
        return members

def save_members_batch(members: List[Dict[str, Any]]):
    """批量保存成员数据"""
    if not members:
        return
    
    with sync_logger.start_timer("批量保存成员数据"):
        connection = None
        cursor = None
        try:
            # 获取已存在的成员ID
            existing_members = get_existing_members()
            
            # 过滤出新成员
            new_members = [m for m in members if m['id'] not in existing_members]
            updated_members = [m for m in members if m['id'] in existing_members]
            
            sync_logger.log_info(f"发现 {len(new_members)} 个新成员，{len(updated_members)} 个已存在成员")
            
            if not new_members and not updated_members:
                sync_logger.log_info("没有新的成员数据需要保存")
                return
            
            connection = get_connection()
            if connection.is_connected():
                cursor = connection.cursor()
                
                # 准备SQL语句
                sql = """
                    INSERT INTO users (
                        id, username, name, email, state, created_at, 
                        last_activity_at, avatar_url, web_url, is_admin,
                        external, organization
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                    ) ON DUPLICATE KEY UPDATE
                        username = VALUES(username),
                        name = VALUES(name),
                        email = VALUES(email),
                        state = VALUES(state),
                        last_activity_at = VALUES(last_activity_at),
                        avatar_url = VALUES(avatar_url),
                        web_url = VALUES(web_url),
                        is_admin = VALUES(is_admin),
                        external = VALUES(external),
                        organization = VALUES(organization)
                """
                
                # 准备批量数据
                values = []
                for member in members:
                    values.append((
                        member['id'],
                        member.get('username', ''),
                        member.get('name', ''),
                        member.get('email', ''),
                        member.get('state', ''),
                        _parse_datetime(member.get('created_at')),
                        _parse_datetime(member.get('last_activity_at')),
                        member.get('avatar_url', ''),
                        member.get('web_url', ''),
                        member.get('is_admin', False),
                        member.get('external', False),
                        member.get('organization', '')
                    ))
                
                # 批量执行
                cursor.executemany(sql, values)
                connection.commit()
                sync_logger.log_info(f"成功保存 {len(members)} 个成员到数据库")
                
        except Error as e:
            sync_logger.log_error(f"批量保存成员数据时发生错误: {e}")
        finally:
            if cursor:
                cursor.close()
            if connection and connection.is_connected():
                connection.close()

def save_project_members_batch(project_id: int, members: List[Dict[str, Any]]):
    """批量保存项目成员关系数据"""
    if not members:
        return
    
    with sync_logger.start_timer(f"批量保存项目 {project_id} 的成员关系"):
        connection = None
        cursor = None
        try:
            # 获取已存在的项目成员关系
            existing_relations = get_existing_project_members()
            
            # 过滤出新关系
            new_relations = []
            for member in members:
                if (project_id, member['id']) not in existing_relations:
                    new_relations.append(member)
            
            sync_logger.log_info(f"项目 {project_id} 发现 {len(new_relations)} 个新成员关系")
            
            if not new_relations:
                sync_logger.log_info(f"项目 {project_id} 没有新的成员关系需要保存")
                return
            
            connection = get_connection()
            if connection.is_connected():
                cursor = connection.cursor()
                
                # 准备SQL语句
                sql = """
                    INSERT INTO project_members (
                        project_id, user_id, access_level, created_at, expires_at
                    ) VALUES (
                        %s, %s, %s, %s, %s
                    ) ON DUPLICATE KEY UPDATE
                        access_level = VALUES(access_level),
                        created_at = VALUES(created_at),
                        expires_at = VALUES(expires_at)
                """
                
                # 准备批量数据
                values = []
                for member in new_relations:
                    values.append((
                        project_id,
                        member['id'],
                        member.get('access_level', 0),
                        _parse_datetime(member.get('created_at')),
                        _parse_datetime(member.get('expires_at'))
                    ))
                
                # 批量执行
                cursor.executemany(sql, values)
                connection.commit()
                sync_logger.log_info(f"成功保存 {len(new_relations)} 个项目成员关系到数据库")
                
        except Error as e:
            sync_logger.log_error(f"批量保存项目成员关系时发生错误: {e}")
        finally:
            if cursor:
                cursor.close()
            if connection and connection.is_connected():
                connection.close()

def sync_all_members():
    """同步所有成员和项目成员关系数据"""
    sync_logger.start_total_timer()
    
    try:
        # 自动建表
        init_tables()
        
        # 获取所有成员
        members = get_all_members()
        
        # 批量保存成员数据
        save_members_batch(members)
        
        # 获取所有GitLab项目
        headers = {'PRIVATE-TOKEN': GITLAB_CONFIG['token']}
        url = f"{GITLAB_CONFIG['url']}/api/{GITLAB_CONFIG['api_version']}/projects"
        params = {'per_page': GITLAB_CONFIG['per_page']}
        response = requests.get(url, headers=headers, params=params, timeout=GITLAB_CONFIG['timeout'])
        projects = response.json()
        
        # 同步每个项目的成员关系
        for project in projects:
            project_members = get_project_members(project['id'])
            save_project_members_batch(project['id'], project_members)
            
        # 更新开发者统计数据
        developer_service = DeveloperService()
        developer_service.sync_developer_statistics()
    finally:
        sync_logger.stop_total_timer()

class DeveloperService:
    def __init__(self):
        self.headers = {'PRIVATE-TOKEN': GITLAB_CONFIG['token']}
        self.base_url = f"{GITLAB_CONFIG['url']}/api/{GITLAB_CONFIG['api_version']}"
        self.db_connection = mysql.connector.connect(**GITLAB_DB_CONFIG)
    
    def get_all_projects(self):
        """获取所有GitLab项目"""
        projects = []
        page = 1
        
        while True:
            url = f"{self.base_url}/projects"
            params = {'page': page, 'per_page': GITLAB_CONFIG['per_page']}
            
            try:
                response = requests.get(url, headers=self.headers, params=params, timeout=GITLAB_CONFIG['timeout'])
                if response.status_code == 200:
                    page_projects = response.json()
                    if not page_projects:
                        break
                    projects.extend(page_projects)
                    page += 1
                else:
                    sync_logger.log_error(f"获取项目列表失败: {response.status_code}")
                    break
            except Exception as e:
                sync_logger.log_error(f"获取项目列表时发生错误: {e}")
                break
        
        return projects
    
    def get_project_members(self, project_id):
        """获取项目成员"""
        return get_project_members(project_id)
    
    def count_commits(self, project_id, user_id):
        """计算用户在项目中的提交数"""
        cursor = self.db_connection.cursor()
        cursor.execute(
            "SELECT COUNT(*) FROM commits WHERE project_id = %s AND author_name = (SELECT username FROM users WHERE id = %s)",
            (project_id, user_id)
        )
        return cursor.fetchone()[0]
    
    def count_mr_created(self, project_id, user_id):
        """计算用户在项目中创建的合并请求数"""
        # 简化处理，实际应该通过API获取
        return 0
    
    def count_mr_merged(self, project_id, user_id):
        """计算用户在项目中合并的合并请求数"""
        # 简化处理，实际应该通过API获取
        return 0
    
    def count_issues_closed(self, project_id, user_id):
        """计算用户在项目中关闭的Issue数"""
        # 简化处理，实际应该通过API获取
        return 0
    
    def save_developer_statistics(self, stats):
        """保存开发者统计数据"""
        try:
            cursor = self.db_connection.cursor()
            
            # 检查是否已存在记录
            cursor.execute(
                "SELECT id FROM developer_statistics WHERE user_id = %s AND project_id = %s AND stats_date = %s",
                (stats['user_id'], stats['project_id'], stats['stats_date'])
            )
            existing = cursor.fetchone()
            
            if existing:
                # 更新现有记录
                sql = """
                    UPDATE developer_statistics SET
                        username = %s,
                        commit_count = %s,
                        mr_created_count = %s,
                        mr_merged_count = %s,
                        mr_reviewed_count = %s,
                        issue_closed_count = %s,
                        review_pass_rate = %s,
                        coverage = %s
                    WHERE id = %s
                """
                cursor.execute(sql, (
                    stats['username'],
                    stats['commit_count'],
                    stats['mr_created_count'],
                    stats['mr_merged_count'],
                    stats['mr_reviewed_count'],
                    stats['issue_closed_count'],
                    stats['review_pass_rate'],
                    stats['coverage'],
                    existing[0]
                ))
            else:
                # 插入新记录
                sql = """
                    INSERT INTO developer_statistics (
                        user_id, username, project_id, commit_count, mr_created_count,
                        mr_merged_count, mr_reviewed_count, issue_closed_count,
                        review_pass_rate, coverage, stats_date
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                    )
                """
                cursor.execute(sql, (
                    stats['user_id'],
                    stats['username'],
                    stats['project_id'],
                    stats['commit_count'],
                    stats['mr_created_count'],
                    stats['mr_merged_count'],
                    stats['mr_reviewed_count'],
                    stats['issue_closed_count'],
                    stats['review_pass_rate'],
                    stats['coverage'],
                    stats['stats_date']
                ))
            
            self.db_connection.commit()
            return True
        except Error as e:
            sync_logger.log_error(f"保存开发者统计数据时发生错误: {e}")
            return False
    
    def sync_developer_statistics(self):
        """同步所有开发者统计数据"""
        # 自动建表
        init_tables()
        
        sync_logger.log_info("开始同步开发者统计数据...")
        today = date.today()
        
        # 获取所有项目
        projects = self.get_all_projects()
        sync_logger.log_info(f"共获取到 {len(projects)} 个项目")
        
        for project in projects:
            project_id = project['id']
            project_name = project['name']
            
            # 获取项目成员
            members = self.get_project_members(project_id)
            sync_logger.log_info(f"项目 {project_name}(ID: {project_id}) 共有 {len(members)} 个成员")
            
            for member in members:
                user_id = member['id']
                username = member.get('username', '')
                
                try:
                    # 统计各种指标
                    commit_count = self.count_commits(project_id, user_id)
                    mr_created_count = self.count_mr_created(project_id, user_id)
                    mr_merged_count = self.count_mr_merged(project_id, user_id)
                    
                    # 构造统计数据
                    stats = {
                        'user_id': user_id,
                        'username': username,
                        'project_id': project_id,
                        'commit_count': commit_count,
                        'mr_created_count': mr_created_count,
                        'mr_merged_count': mr_merged_count,
                        'mr_reviewed_count': 0,  # 暂不统计
                        'issue_closed_count': 0,  # 暂不统计
                        'review_pass_rate': 0.0,  # 暂不统计
                        'coverage': 0.0,  # 暂不统计
                        'stats_date': today
                    }
                    
                    # 保存统计数据
                    self.save_developer_statistics(stats)
                    sync_logger.log_info(f"已保存用户 {username}(ID: {user_id}) 在项目 {project_name} 的统计数据")
                except Exception as e:
                    sync_logger.log_error(f"处理用户 {username}(ID: {user_id}) 在项目 {project_name} 的统计数据时发生错误: {e}")
        
        sync_logger.log_info("开发者统计数据同步完成")

if __name__ == "__main__":
    sync_all_members() 